#!/usr/bin/perl
use strict;
use DBI;
use XML::DOM;

my $dsn = "DBI:mysql:database=stockdabase;host=localhost;";
my $dbh = DBI->connect($dsn, "root","38574638") or die;

my $yahoo  ;
my $sth=$dbh->prepare("set character_set_client=?");
$sth->execute("utf8");
$sth = $dbh->prepare("delete from marryagedb");
$sth->execute();
$sth = $dbh->prepare("delete from result");
$sth->execute();
$sth = $dbh->prepare("select * from  stock order by yahoo ");
$sth->execute();
while(my $ref= $sth->fetchrow_hashref() )
{
	unlink("CVS");
	$yahoo =  $ref->{"yahoo"};
	`wget  http://table.finance.yahoo.com/table.csv?s=$yahoo -O CVS --read-timeout=15`;
	if ( -e  "CVS")
	{
		open(FILE,"CVS") ;
		my $string =<FILE>;
		while($string = <FILE>)
		{
			chomp($string);
			my @array=  split(",",$string);
			
			my $f = $dbh->prepare("insert into marryagedb (id,stockid,stockdate,open,high,low,close,volume,adj) values  (?,?,?,?,?,?,?,?,?)");
			if($array[5] !=0)
			{
			$f->execute(undef,$yahoo,$array[0],$array[1],$array[2],$array[3],$array[4],$array[5],$array[6]) ; 
		}

		}
		close(FILE);
		unlink("CVS");
	}
	else
	{
		print $yahoo,"\n";
	}
}
$sth = $dbh->prepare("delete  from  marryagedb where  YEAR(stockdate)  <  ? ");
$sth->execute(2008);
$dbh->disconnect();
